﻿/*
LICENCE___________________________________________________________________
    Copyright (C) 2012 Reverdy Sylvain

    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 2 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License along
    with this program; if not, write to the Free Software Foundation, Inc.,
    51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
FIN LICENCE______________________________________________________________
*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using Merise_designer.Methodes;
using Finisar.SQLite;



namespace Merise_designer.Donnees
{
    public class CL_Connexion
    {
        private static CL_Connexion instance = null;
        private static readonly object mylock = new object();//si on travaille sur plusieur thread on empéche les thread d'utiliser une instance de notre Singleton en même temps
        public SQLiteConnection cnn;

        private CL_Connexion(String bddFIle,Boolean modeCreation)
        {

            string strSQLiteConn = @"Data Source=" + bddFIle + ";Version=3;New=False;Compress=True";
            if (modeCreation) strSQLiteConn = @"Data Source=" + bddFIle + ";Version=3;New=True;Compress=True";
            
            //string strAccessConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Password=;User ID=Admin;Data Source=" + Openroject.getActualProjectFIle();
            try
            {
                //cnn = new OleDbConnection(strAccessConn);
                cnn = new SQLiteConnection(strSQLiteConn);

            }
            catch (Exception ex)
            {
                MessageBox.Show("Erreur de connexion : " + Convert.ToString(ex.Message));
                //MessageBox.Show("Le fichier de base de données est introuvable !", "Erreur Connexion BDD!", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }

        }

        public static CL_Connexion GetInstance(String bddFile,Boolean modeCreation)
        {
            lock ((mylock))
            {

                if (!modeCreation)
                {
                    if (instance == null || Openroject.isCHanged())
                    {
                        instance = new CL_Connexion(bddFile, modeCreation);
                        Openroject.SetisCHanged(false);
                    }
                }
                else
                {
                    return new CL_Connexion(bddFile,modeCreation);
                }
                return instance;
            }
        }
    }

    public class CL_CAD
    {
        public DataSet GetRows(string rqSql, string rows_name)
        {
            DataSet returnValue = null;

            CL_Connexion cnn = CL_Connexion.GetInstance(Openroject.getActualProjectFIle(),false);
            try
            {
                cnn.cnn.Open();
                DataSet myDataSet = new DataSet();
                SQLiteCommand mySQLiteCommand = new SQLiteCommand(rqSql, cnn.cnn);
                SQLiteDataAdapter myDataAdapter = new SQLiteDataAdapter(mySQLiteCommand);

                myDataAdapter.Fill(myDataSet, rows_name);
                returnValue = myDataSet;
                cnn.cnn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Erreur de connexion : " + Convert.ToString(ex.Message));
                cnn.cnn.Close();
            }

            return returnValue;
        }

        public int ActionsRows(string rqSql, bool afficheErreur)
        {
            CL_Connexion cnn = CL_Connexion.GetInstance(Openroject.getActualProjectFIle(), false);

            try
            {
                cnn.cnn.Open();
                SQLiteCommand mySQLiteCommand = new SQLiteCommand(rqSql, cnn.cnn);
                SQLiteDataAdapter myDataAdapter = new SQLiteDataAdapter(mySQLiteCommand);
                mySQLiteCommand.ExecuteNonQuery();
                cnn.cnn.Close();
            }
            catch (Exception ex)
            {
                if (afficheErreur)
                {
                    if (MessageBox.Show("Erreur : " + Convert.ToString(ex.Message) + "\n\n Voulez vous continuer ?", "Erreur", MessageBoxButtons.YesNo) == DialogResult.No)
                    {
                        cnn.cnn.Close();
                        return 0;
                    }

                    cnn.cnn.Close();
                    return 1;
                }
                cnn.cnn.Close();
                return 1;
            }
            return 1;
        }

        public int ActionsRowsTransact(string rqSql, bool afficheErreur)
        {
            CL_Connexion cnn = CL_Connexion.GetInstance(Openroject.getActualProjectFIle(), false);
            String req = "BEGIN TRANSACTION;" + rqSql + "COMMIT;";
            try
            {
                cnn.cnn.Open();
                SQLiteCommand mySQLiteCommand = new SQLiteCommand(req, cnn.cnn);
                SQLiteDataAdapter myDataAdapter = new SQLiteDataAdapter(mySQLiteCommand);
                mySQLiteCommand.ExecuteNonQuery();
                cnn.cnn.Close();
            }
            catch (Exception ex)
            {
                if (afficheErreur)
                {
                    if (MessageBox.Show("Erreur : " + Convert.ToString(ex.Message) + "\n\n Voulez vous continuer ?", "Erreur", MessageBoxButtons.YesNo) == DialogResult.No)
                    {
                        cnn.cnn.Close();
                        return 0;
                    }

                    cnn.cnn.Close();
                    return 1;
                }
                cnn.cnn.Close();
                return 1;
            }
            return 1;
        }

        /// <summary>
        /// Créer une base de données
        /// </summary>
        /// <param name="rqSql"></param>
        /// <param name="afficheErreur"></param>
        /// <returns>0 si erreur, 1 si succés</returns>
        public int CreateBDD(String bddFile,string rqSql, bool afficheErreur)
        {
            CL_Connexion cnn = CL_Connexion.GetInstance(bddFile,true);

            try
            {
                cnn.cnn.Open();
                SQLiteCommand mySQLiteCommand = new SQLiteCommand(rqSql, cnn.cnn);
                SQLiteDataAdapter myDataAdapter = new SQLiteDataAdapter(mySQLiteCommand);
                mySQLiteCommand.ExecuteNonQuery();
                cnn.cnn.Close();
            }
            catch (Exception ex)
            {
                if (afficheErreur)
                {
                    if (MessageBox.Show("Erreur : " + Convert.ToString(ex.Message) + "\n\n Voulez vous continuer ?", "Erreur", MessageBoxButtons.YesNo) == DialogResult.No)
                    {
                        cnn.cnn.Close();
                        return 0;
                    }

                    cnn.cnn.Close();
                    return 1;
                }
                cnn.cnn.Close();
                return 1;
            }
            return 1;
        }
    }
}
